const mysql = require("mysql");

const db = mysql.createPool({
  host: "127.0.0.1", //如果数据库部署在linux上，只需将IP换为Linux服务器的IP即可
  user: "root",
  password: "8023China!",
  database: "my_db_01",
});

//数据查询
db.query("select * from users ", (err, result) => {
  if (err) return console.log(err.message);
  console.log(result);
});

//数据插入
const user = { username: "spider-man", password: "124354" };

//原始写法
// const sqlStr = "INSERT INTO users (username, password) VALUES (?, ?)";
// db.query(sqlStr, [user.username, user.password], (err, results) => {
//   if (err) return console.log(err.message);
//   if (results.affectedRows === 1) {
//     console.log("insert data successfully");
//   }
// });

// 简易写法
const sqlStr = "INSERT INTO users Set ?";
db.query(sqlStr, user, (err, results) => {
  if (err) return console.log(err.message);
  if (results.affectedRows === 1) {
    console.log("insert data successfully");
  }
});

// 数据更新
const user2 = { id: 9, username: "jessica", password: "268882" };
const sqlStr2 = "UPDATE USERS set username= ?, password= ? where id = ?";
db.query(
  sqlStr2,
  [user2.username, user2.password, user2.id],
  (err, results) => {
    if (err) return console.log(err.message);
    if (results.affectedRows === 1) {
      console.log("updated data successfully");
    }
  }
);

// 数据更新便捷方式
const user3 = { id: 11, username: "Monica", password: "263882" };
const sqlStr3 = "UPDATE USERS set ? where id = ?";
db.query(sqlStr3, [user3, user3.id], (err, results) => {
  if (err) return console.log(err.message);
  if (results.affectedRows === 1) {
    console.log("updated data22 successfully");
  }
});

// 删除数据， 尽量根据id这样的唯一标识来删除对应的数据
// delete 会真正把数据从表里删除，不推荐
// const sqlStr4 = "DELETE FROM users WHERE id=?";
// db.query(sqlStr4, 13, (err, results) => {
//   if (err) return console.log(err.message);
//   if (results.affectedRows === 1) {
//     console.log("deleted the data successfully");
//   }
// });

// 建议使用标记删除法
